*** Update – Just wanted to say that dynamic data masking has the potential of being a great feature and one I have been wanting for years. That point did not come across in the expediency of writing this post. ****

I have a mere 5 minutes before I shutdown and deallocate my SQL Server 2016 CTP 2 Azure VM where I have been playing around with Dynamic Data Masking, a new feature. I love new SQL Server features in new versions and I have put aside time with every release to experiment with these new features since the early days of SQL 2005. And since there was not much to speak of with SQL Server Reporting Services in this CTP for SQL Server 2016 I thought I would take 15 minutes to learn about dynamic data masking.

It really only took about 15 minutes to understand everything I needed to know and Books Online does a great job at summing this new feature up.

https://msdn.microsoft.com/en-us/library/mt130841(v=sql.130).aspx

Here is a minute by minute breakdown of my path to expertise with dynamic data masking.

Minutes 1 – 3 – Find info in BOL regarding dynamic data masking

Minutes 3 – 4 – Read everything there is to know about dynamic data masking in BOL an run the required DBCC command to enable DDM:

Minutes 4 – 10 – Create a basic table in SQL Server 2016. I called it Confidential and added 5 columns tantalizingly named

CREATE TABLE [dbo].[Confidential](

[ID] [int] NULL,

[Name] [nvarchar](70)NULL,

[CreditCard] [varchar](9)NULL,

[Salary] [int] NULL,

[Email] [nvarchar](60)NULL

)ON [PRIMARY]

Populate it with confidential data like my real credit card info and salary.

Add in a friend’s (Shawn Mcgehee’s) real card info and salary. Don’t tell him I am writing a quick blog about it and using his name and credit card info and don’t tell him how I came by this info.

Query that information logged in as a sysadmin.

Mask the data with examples from BOL. There are three ways to mask the data: default(), email() and partial(), each producing different masked valued for different data types either text or numeric values, partial() allowing you to customize the masking somewhat with a prefix and suffix of the actual data for character data.

ALTER Table Confidential

ALTER COLUMN SALARY ADD MASKED WITH (FUNCTION=’default()’)

ALTER Table Confidential

ALTER COLUMN creditcard ADD MASKED WITH (FUNCTION=’partial(2,”XXXX”,2)’)

ALTER Table Confidential

ALTER COLUMN email ADD MASKED WITH (FUNCTION=’email()’)

Create a user named Randy that has SELECT permission on the Confidential table.

CREATE USER Randy WITHOUT LOGIN;

GRANT SELECT ON Confidential TO Randy;

Execute a select statement as Randy.

EXECUTE AS USER=’Randy’;

SELECT * FROM Confidential;

REVERT;

See the outcome:

Noted the masking.

Minutes 10 – 12 – Discover that an ad hoc query can easily reveal the unmasked contents just by casting the data to an unsupported data type:

EXECUTE AS USER=’Randy’;

SELECT cast(email as nchar(40))as email, email FROM Confidential;

REVERT;

This last query shows the real email address as well as the masked email. Sooooo, contemplate for another 30 seconds.

Minutes 12 – 15 – Read in BOL that ad hoc queries should not be allowed if you want to protect the masked data from the previous CAST statement,  so I created a stored procedure:

Create proc spConfidential

AS

SELECT ID, Name, CreditCard, Salary, Email

FROM Confidential

Granted execute to Randy.

Noted that the data was masked as expected.

I am going to spend the next several days and minutes not thinking about dynamic data masking as I will be on a cruise in the Mediterranean starting tomorrow but at least wanted to share a few minutes of the fun you can have with a new feature in SQL Server 2016.  Next up will be the Query Store. I plan to spend at least 300 minutes therein, at least until the next CTP where I am hopeful SSRS will have some of its own new features.